package com.pump;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.googlecode.aviator.AviatorEvaluator;
import com.pump.common.ExcelUtil;
import com.pump.data.PumpTestSourceData;
import com.pump.data.PumpspectrumData;
import com.pump.data.PumptestData;

import Jama.Matrix;

public class Importor {

	static double[][] paramSrcH = {
			{0.0000000000021282381,-2.13557765543878E-09,-0.0000126176506395457,0.00135616728459087,28.6822697305721},
			{1.96100142945727E-12,1.55062356931433E-09,-0.0000134661875392535,0.0026442306482786,30.7434605874154},
			{1.83686037724422E-12,-0.0000000017058801921,-0.0000123614837515299,0.00244548585207036,33.378034162427},
			{7.966537286229E-13,-1.08598673149236E-09,-9.79493310014264E-06,0.000653500996735973,36.6039856557281},
			{0.0000000000001775747,-1.05121541946481E-09,-0.0000073437182594925,-0.00117067397210895,43.3338492466677}
	};
	
	static double[][] paramSrcP = {{}};
	
	static double [][]  paramSrcE = {
			{-0.000000000019780964211282,	0.0000000289993505309759, -0.0000723450559636405, 0.14716016052413,   -0.847359713815942},
			{-0.0000000000140859710414022, 	0.0000000258551125489364, -0.0000783517017493209, 0.152962301193509,  -0.687765630577276}, 
			{-0.00000000000344794363971326, 0.00000000492317133510821,-0.0000647083891798628, 0.149594488000071,  -0.172059972895209} ,
			{-0.0000000000184136006206903, 	0.0000000531953436018197, -0.000109694317899214,  0.162258346772234 , -0.495250792844111}, 
			{-0.0000000000152987407051182, 	0.0000000514603779048288, -0.000112550823127404,  0.162543363165126,  -0.207757929914487}
	
	};
	
	static double [][] paramSrcNSHr={
			
			{-0.00000000288010510810423, 	0.0000130807322032947, 	    -0.0155214849308634, 	10.1017308974703}, 
			{-0.00000000168046128555321, 	0.0000105582157117353,   	-0.0139567796575615, 	9.7078379813102}, 
			{-0.000000000241426235018125, 	0.00000728904676001742, 	-0.0117881045998661,	9.11699860707009 },
			{0.000000000859379683970344, 	0.00000453374081406058, 	-0.00980792625643411, 	8.52615923282998 },
			{0.00000000223086732895687, 	0.000000584474476719408, 	-0.00664395456899701, 	7.46264835919778 },

	};
	
	public static double calcValue(double[] param,Double q)
	{		
		double value = 0.0f;		
		for(int i = 0 ;i<param.length ;i++)
		{
			value  +=  param[i]*Math.pow(q,param.length-1-i);
		}
		return value;
	}
	
	public static void printInfo(String type,int j,double q,double[] param,double value)
	{
		StringBuilder sb = new StringBuilder();
		sb.append(type);
		sb.append("第"+j+"条数据,");
		sb.append("流量："+q+",");
		/*sb.append("参数为：");
		for(int i = 0;i< param.length;i++)
		{
		    sb.append(param[i]+",");
		}*/
	    sb.append("\r 计算值:"+value);
	    System.out.println(sb.toString());
	}
	
	public static void testSourceH(List<PumpspectrumData> list)
	{
		//double[] q = {800.0,850.0,900.0,950.0,1000.0,1050.0,1100.0,1150.0};
		
		double[] q = {150.665,
				299.141,
				445.1,
				601.248,
				749,
				895.415,
				995.555,
				1096.29,
				1190.681,
				1290.713,
				1390.674,
				1488.585,
				1592.909,
				1686.168
};
	
		
		//System.out.println("原有数据的计算");
		//System.out.println("验证扬程参数:");
		
		for(int j = 0; j < paramSrcH.length;j++ )
		{
		    for(int i =0 ;i < q.length;i++)
		    {
		    	double value = calcValue(paramSrcH[j],q[i]);
		    	printInfo("原有数据计算扬程:",j,q[i],paramSrcH[j],value);
		    	
		    }
		}
		
		System.out.println("-----------------------------------------------------");
		PumpspectrumData data = list.get(0);
		List<PumptestData> testDataList = data.getPumptestDataList();
		
		testDataList.sort(new Comparator<PumptestData>(){

			public int compare(PumptestData o1, PumptestData o2) {
				// TODO Auto-generated method stub
				return Double.compare(o1.getSegDia(),o2.getSegDia());
			}
			
		});
		
		for(int j = 0 ;j < testDataList.size();j++)
		{
			System.out.println(testDataList.get(j).getSegDia());
            for(int i =0 ;i < q.length;i++)
			{
            	double[] paraH = new double[5];
            	paraH[0] = testDataList.get(j).getParHa();
            	paraH[1] = testDataList.get(j).getParHb();
            	paraH[2] = testDataList.get(j).getParHc();
            	paraH[3] = testDataList.get(j).getParHd();
            	paraH[4] = testDataList.get(j).getParHe();
            	double value = calcValue(paraH,q[i]);
            	printInfo("当前算法得出的参数计算扬程:",j,q[i],paraH,value);
			}
		}
		
		
		/*Double[] paramH={0.0000000000021282381,-2.13557765543878E-09,-0.0000126176506395457,0.00135616728459087,28.6822697305721};
		Double h = calcValue(paramH,800.0);
		System.out.println(h);
		h = calcValue(paramH,900.0);
		System.out.println(h);
		h = calcValue(paramH,1000.0);
		System.out.println(h);
		
		
		System.out.println("``````````````````````````````````````");
		
		Double[] paramH1={6.674532434179174e-12,-1.616482621886133e-8,0.0000010988400543762256,-0.0030606606135955144,28.920194302867614};
		h = calcValue(paramH,800.0);
	    System.out.println(h);
		h = calcValue(paramH,900.0);
		System.out.println(h);
		h = calcValue(paramH,1000.0);
		System.out.println(h);*/

	}
		
	
	public static void testSourceE(List<PumpspectrumData> list)
	{
		/*double[] q ={150.665,
		299.141,
		445.1,
		601.248,
		749,
		895.415,
		995.555,
		1096.29,
		1190.681,
		1290.713,
		1390.674,
		1488.585,
		1592.909,
		1686.168};*/

		double[] q = {800.0,850.0,900.0,950.0,1000.0,1050.0,1100.0,1150.0};
	
		
		for(int j = 0; j < paramSrcE.length;j++ )
		{
		    for(int i =0 ;i < q.length;i++)
		    {
		    	double value = calcValue(paramSrcE[j],q[i]);
		    	printInfo("原有数据计算扬程:",j,q[i],paramSrcE[j],value);		    	
		    }
		}
		
		System.out.println("-----------------------------------------------------");
		PumpspectrumData data = list.get(0);
		List<PumptestData> testDataList = data.getPumptestDataList();
		
		testDataList.sort(new Comparator<PumptestData>(){

			public int compare(PumptestData o1, PumptestData o2) {
				// TODO Auto-generated method stub
				return Double.compare(o1.getSegDia(),o2.getSegDia());
			}
			
		});
		
		for(int j = 0 ;j < testDataList.size();j++)
		{
            for(int i =0 ;i < q.length;i++)
			{
            	double[] para = new double[5];
            	para[0] = testDataList.get(j).getParEa();
            	para[1] = testDataList.get(j).getParEb();
            	para[2] = testDataList.get(j).getParEc();
            	para[3] = testDataList.get(j).getParEd();
            	para[4] = testDataList.get(j).getParEe();
            	double value = calcValue(para,q[i]);
            	printInfo("当前算法得出的参数计算效率:",j,q[i],para,value);
			}
		}
	}
	
	
	public static void testSourceN(List<PumpspectrumData> list)
	{


		double[] q = {439.13,1071.92,1269.22,1459.27,1651.48};
		//double[] q = {800.0,850.0,900.0,950.0,1000.0,1050.0,1100.0,1150.0,1200,1250,1300,1350,1400,1450,1500};
	
		
		for(int j = 0; j < paramSrcNSHr.length;j++ )
		{
		    for(int i =0 ;i < q.length;i++)
		    {
		    	double value = calcValue(paramSrcNSHr[j],q[i]);
		    	printInfo("原有数据计算扬程:",j,q[i],paramSrcNSHr[j],value);
		    	
		    }
		}
		
		System.out.println("-----------------------------------------------------");
		PumpspectrumData data = list.get(0);
		List<PumptestData> testDataList = data.getPumptestDataList();
		
		testDataList.sort(new Comparator<PumptestData>(){

			public int compare(PumptestData o1, PumptestData o2) {
				// TODO Auto-generated method stub
				return Double.compare(o1.getSegDia(),o2.getSegDia());
			}
			
		});
		
		for(int j = 0 ;j < testDataList.size();j++)
		{
			System.out.print(testDataList.get(j).getSegDia());
            for(int i =0 ;i < q.length;i++)
			{
            	double[] para = new double[4];
            	para[0] = testDataList.get(j).getParNPSHa();
            	para[1] = testDataList.get(j).getParNPSHb();
            	para[2] = testDataList.get(j).getParNPSHc();
            	para[3] = testDataList.get(j).getParNPSHd();
             	double value = calcValue(para,q[i]);
            	printInfo("当前算法得出的参数计算效率:",j,q[i],para,value);
			}
		}
	}
	
	
	public static void main(String[] args) {
		List<PumpspectrumData> list = getPumpData("KP","E:\\xuyunjia\\2017\\水泵项目\\KP.xls");
		String str = JSON.toJSONString(list, SerializerFeature.WriteMapNullValue);		
		System.out.println(str);
		
		
		
		//testSourceH(list);
		//testSourceE(list);
		testSourceN(list);
	}

	public static Double getPumpDataValues(String str,Map<String,Object> env)
	{		
		return 0.0;
	}
	
	private static PumptestData getPumptestData(PumpTestSourceData testSourceData) {
		PumptestData pumpTestData = new PumptestData();
		Double[] paramH = getCaculateParams(testSourceData.getPowerDataList(), "h", 5);
		Double[] paramP = getCaculateParams(testSourceData.getPowerDataList(), "power", 5);
		Double[] paramE = getCaculateParams(testSourceData.getPowerDataList(), "e", 5);
		pumpTestData.setParHa(paramH[0]);
		pumpTestData.setParHb(paramH[1]);
		pumpTestData.setParHc(paramH[2]);
		pumpTestData.setParHd(paramH[3]);
		pumpTestData.setParHe(paramH[4]);

		pumpTestData.setParEa(paramE[0]);
		pumpTestData.setParEb(paramE[1]);
		pumpTestData.setParEc(paramE[2]);
		pumpTestData.setParEd(paramE[3]);
		pumpTestData.setParEe(paramE[4]);

		pumpTestData.setParPa(paramP[0]);
		pumpTestData.setParPb(paramP[1]);
		pumpTestData.setParPc(paramP[2]);
		pumpTestData.setParPd(paramP[3]);
		pumpTestData.setParPe(paramP[4]);
		return pumpTestData;
	}

	private static Double[] getPumpNPSHRtestData(PumpTestSourceData testSourceData) {

		Double[] paramNPSHR = getCaculateParams(testSourceData.getNpshrDataList(), "npshr", 4);
		return paramNPSHR;

	}
	
	private static Double getMinQForECurve(Double[] eParam,Double minQ,Double maxQ)
	{
		Double min=minQ;
		Double max=maxQ;
		
		Double point = minQ;
		Double maxEff= Double.MIN_VALUE;

		while(true)
		{
			Double eff = eParam[0]*Math.pow(min, 4)+eParam[1]*Math.pow(min, 3)+eParam[2]*Math.pow(min, 2)+eParam[3]*Math.pow(min, 1)+eParam[4];
		
			if(Double.compare(maxEff, eff)<0)				
			{
				maxEff =eff;
				point = min;
			}			
			min+=0.01;
			
			if(Double.compare(min, max)>=0)
			{
				break;
			}
		}
		return point;
	}

	private static Double[] getPumpNPSHRtestData(List<PumpTestSourceData> testSourceDataList,
			List<PumptestData> testDataList, int index) {
		int maxIndex = testSourceDataList.size() - 1;
		Double minDia = testSourceDataList.get(0).getDia();
		Double maxDia = testSourceDataList.get(maxIndex).getDia();

		Double curDia = testSourceDataList.get(index).getDia();

		Double d = maxDia - minDia;
		Double minRate = (curDia - minDia) / d;
		Double maxRate = (maxDia - curDia) / d;

		List<Map<String, Double>> minNpshrDataList = testSourceDataList.get(0).getNpshrDataList();
		List<Map<String, Double>> maxNpshrDataList = testSourceDataList.get(maxIndex).getNpshrDataList();

		//List<Map<String, Double>> dataList = new LinkedList<Map<String, Double>>();

		List<Map<String, Double>> npshrDataList = new LinkedList<Map<String, Double>>();

		for (int i = 0; i < minNpshrDataList.size(); i++)

		{
			Map<String, Double> m = minNpshrDataList.get(i);
			Map<String, Double> data = new HashMap<String, Double>();
			Double q = m.get("q");
			Double minNpshr = m.get("npshr");

			// 求q对应下的最大值
			PumptestData testData = testDataList.get(testDataList.size() - 1);

			testSourceDataList.get(0).getDia();

			Double nPSHa = testData.getParNPSHa();
			Double nPSHb = testData.getParNPSHb();
			Double nPSHc = testData.getParNPSHc();
			Double nPSHd = testData.getParNPSHd();

			Double maxNpshr = nPSHa * Math.pow(q, 3) + nPSHb * Math.pow(q, 2) + nPSHc * Math.pow(q, 1) + nPSHd;

			Double npshr = maxRate * maxNpshr + minRate * minNpshr;
			data.put("q", q);
			data.put("npshr", npshr);
			npshrDataList.add(data);
		}

		for (Map<String, Double> m : maxNpshrDataList) {
			Map<String, Double> data = new HashMap<String, Double>();
			Double q = m.get("q");
			Double maxNpshr = m.get("npshr");

			// 求q对应下的最大值
			PumptestData testData = testDataList.get(0);

			Double nPSHa = testData.getParNPSHa();
			Double nPSHb = testData.getParNPSHb();
			Double nPSHc = testData.getParNPSHc();
			Double nPSHd = testData.getParNPSHd();

			Double minNpshr = nPSHa * Math.pow(q, 3) + nPSHb * Math.pow(q, 2) + nPSHc * Math.pow(q, 1) + nPSHd;

			Double npshr = maxRate * maxNpshr + minRate * minNpshr;

			data.put("q", q);
			data.put("npshr", npshr);
			npshrDataList.add(data);
		}

		Double[] paramNPSHR = getCaculateParams(npshrDataList, "npshr", 4);

		return paramNPSHR;

	}




	public static List<PumpspectrumData> getPumpData(String tableName,String fileName) {
		// 从Excel中加载测试数据
		List<PumpspectrumData> pumpDataList = getDataFromExcel(fileName);
		
		//System.out.println(pumpDataList);
		
		// 遍历数据
		for(int j = 0 ; j < pumpDataList.size();j++)

		{
			PumpspectrumData data  = pumpDataList.get(j);
			
			List<PumpTestSourceData> pumpTestSourceDataList = data.getTestSourceDataList();
			List<PumptestData> pumpTestDataList = new LinkedList<PumptestData>();
			int length = pumpTestSourceDataList.size();
						

			// 计算流量扬程，流量效率，流量功率的参数
			double minDia = pumpTestSourceDataList.get(0).getDia();
			double maxDia = pumpTestSourceDataList.get(length - 1).getDia();
			
			// 计算最小直径相关的值
			PumpTestSourceData testSourceData = pumpTestSourceDataList.get(0);
			//Double minDia = testSourceData.getDia();
			PumptestData testData = getPumptestData(testSourceData);			
			Double[] npshrParamMin = getPumpNPSHRtestData(pumpTestSourceDataList.get(0));
			data.setTableName(tableName);
			testData.setParNPSHa(npshrParamMin[0]);
			testData.setParNPSHb(npshrParamMin[1]);
			testData.setParNPSHc(npshrParamMin[2]);
			testData.setParNPSHd(npshrParamMin[3]);
			testData.setSegDia(minDia);
			
			
			//求出最小直径的最小流量和最大流量
			double curMaxEffQ = getMinQForECurve(
					new Double[]{testData.getParEa(),testData.getParEb(),testData.getParEc(),testData.getParEd(),testData.getParEe()},
					testSourceData.getPowerDataList().get(0).get("q"),
					testSourceData.getPowerDataList().get(testSourceData.getPowerDataList().size()-1).get("q"));
			double curDiaMinQ = curMaxEffQ * data.getmCSF();
			
			double curDiaMinH = testData.getParHa()*Math.pow(curDiaMinQ, 4)+testData.getParHb()*Math.pow(curDiaMinQ, 3)+
					testData.getParHc()*Math.pow(curDiaMinQ, 2)+testData.getParHd()*Math.pow(curDiaMinQ, 1)+testData.getParHe();
			
			double curDiaMaxQ = testSourceData.getPowerDataList().get(testSourceData.getPowerDataList().size()-1).get("q")/1.05;
		    double curDiaMaxH = testData.getParHa()*Math.pow(curDiaMaxQ, 4)+testData.getParHb()*Math.pow(curDiaMaxQ, 3)+
					testData.getParHc()*Math.pow(curDiaMaxQ, 2)+testData.getParHd()*Math.pow(curDiaMaxQ, 1)+testData.getParHe();
			
			pumpTestDataList.add(testData);
			
			
			data.setMinDia(minDia);
			data.setMaxDia(maxDia);
			data.setMinDiaparaHa(testData.getParHa());
			data.setMinDiaparaHb(testData.getParHb());
			data.setMinDiaparaHc(testData.getParHc());
			data.setMinDiaparaHd(testData.getParHd());
			data.setMinDiaparaHe(testData.getParHe());
			data.setMinDiaMinQ(curDiaMinQ);
			data.setMinDiaMaxQ(curDiaMaxQ);
			data.setMinDiaMinH(curDiaMinH);
			data.setMinDiaMaxH(curDiaMaxH);

			// 计算最大直径相关的值
			testSourceData = pumpTestSourceDataList.get(length - 1);
			//Double maxDia = testSourceData.getDia();
			testData = getPumptestData(testSourceData);
			Double[] npshrParamMax = getPumpNPSHRtestData(testSourceData);
			testData.setParNPSHa(npshrParamMax[0]);
			testData.setParNPSHb(npshrParamMax[1]);
			testData.setParNPSHc(npshrParamMax[2]);
			testData.setParNPSHd(npshrParamMax[3]);
			testData.setSegDia(maxDia);

			
			curMaxEffQ = getMinQForECurve(
					new Double[]{testData.getParEa(),testData.getParEb(),testData.getParEc(),testData.getParEd(),testData.getParEe()},
					testSourceData.getPowerDataList().get(0).get("q"),
					testSourceData.getPowerDataList().get(testSourceData.getPowerDataList().size()-1).get("q"));
            curDiaMinQ = curMaxEffQ * data.getmCSF();
			
			curDiaMinH = testData.getParHa()*Math.pow(curDiaMinQ, 4)+testData.getParHb()*Math.pow(curDiaMinQ, 3)+
					testData.getParHc()*Math.pow(curDiaMinQ, 2)+testData.getParHd()*Math.pow(curDiaMinQ, 1)+testData.getParHe();
			
			curDiaMaxQ = testSourceData.getPowerDataList().get(testSourceData.getPowerDataList().size()-1).get("q")/1.05;
			curDiaMaxH = testData.getParHa()*Math.pow(curDiaMaxQ, 4)+testData.getParHb()*Math.pow(curDiaMaxQ, 3)+
					testData.getParHc()*Math.pow(curDiaMaxQ, 2)+testData.getParHd()*Math.pow(curDiaMaxQ, 1)+testData.getParHe();
			
			pumpTestDataList.add(testData);
			
			data.setMaxDia(maxDia);
			data.setMinDia(minDia);
			data.setMaxDia(maxDia);
			data.setMaxDiaparaHa(testData.getParHa());
			data.setMaxDiaparaHb(testData.getParHb());
			data.setMaxDiaparaHc(testData.getParHc());
			data.setMaxDiaparaHd(testData.getParHd());
			data.setMaxDiaparaHe(testData.getParHe());
			data.setMaxDiaMinQ(curDiaMinQ);
			data.setMaxDiaMaxQ(curDiaMaxQ);
			data.setMaxDiaMinH(curDiaMinH);
			data.setMaxDiaMaxH(curDiaMaxH);

			// 计算除了最大直径和最小直径外的测试数据的你和参数
	
			for (int i = 1; i < length - 1; i++) {
				testSourceData = pumpTestSourceDataList.get(i);
				
				testData = getPumptestData(testSourceData);

				Double[] npshrParam = getPumpNPSHRtestData(pumpTestSourceDataList, pumpTestDataList, i);
				testData.setParNPSHa(npshrParam[0]);
				testData.setParNPSHb(npshrParam[1]);
				testData.setParNPSHc(npshrParam[2]);
				testData.setParNPSHd(npshrParam[3]);				
				testData.setSegDia(testSourceData.getDia());				
							
				pumpTestDataList.add(testData);
			}
			
			for (int i = 0; i < length ; i++)
			{
				testData = pumpTestDataList.get(i);
				testData.setTableName(tableName);
				testData.setFrequency(data.getFrequency());
				testData.setPoles(data.getPoles());
				testData.setType(data.getType());
				testData.setImpellerMaterial(data.getImpellerMaterial());
				testData.setRotorSpeed(Double.valueOf(data.getRotorSpeed().toString()));				
				testData.setMinDia(minDia);
				testData.setMaxDia(maxDia);
				testData.setMinDiaMinQ(data.getMinDiaMinQ());
				testData.setMinDiaMaxQ(data.getMinDiaMaxQ());
				testData.setMaxDiaMinQ(data.getMaxDiaMinQ());
				testData.setMaxDiaMaxQ(data.getMaxDiaMaxQ());
			}

			// 计算
			data.setPumptestDataList(pumpTestDataList);

		}
		return pumpDataList;
	}



	public static PumpTestSourceData readPowerData(Sheet sheet, int startIndex) {
		int maxCount = 20;
		List<Map<String, Double>> powerList = new LinkedList<Map<String, Double>>();
		List<Map<String, Double>> npshrList = new LinkedList<Map<String, Double>>();
		PumpTestSourceData testSourceData = new PumpTestSourceData();

	
		
		Double diaCell = ExcelUtil.readDoubleCellValue(sheet,startIndex,1);
		if(diaCell==null)
		{
			return null;
		}

		testSourceData.setDia(diaCell);

		for (int i = startIndex; i < startIndex + maxCount; i++) {
			Double q = ExcelUtil.readDoubleCellValue(sheet,i,2);
			Double h = ExcelUtil.readDoubleCellValue(sheet,i,3);
			Double power = ExcelUtil.readDoubleCellValue(sheet,i,4);
			Double e = ExcelUtil.readDoubleCellValue(sheet,i,5);
			Double q4npshr = ExcelUtil.readDoubleCellValue(sheet,i,6);
			Double npshr = ExcelUtil.readDoubleCellValue(sheet,i,7);

			boolean isPowerNull = (q == null && h == null && power == null && e == null);

			boolean isNpshr = (q4npshr == null && npshr == null);

			if (isPowerNull && isNpshr) {
				break;
			}

			if ((q != null && h != null && power != null && e != null)) {
				Map<String, Double> m = new HashMap<String, Double>();
				m.put("q", q);
				m.put("h", h);
				m.put("power", power);
				m.put("e", e);
				powerList.add(m);
			}
			if ((q4npshr != null && npshr != null)) {
				Map<String, Double> m = new HashMap<String, Double>();
				m = new HashMap<String, Double>();
				m.put("q", q4npshr);
				m.put("npshr", npshr);
				npshrList.add(m);
			}
		}
		testSourceData.setNpshrDataList(npshrList);
		testSourceData.setPowerDataList(powerList);

		return testSourceData;
	}

	public static List<PumpspectrumData> getDataFromExcel(String fileName) {
		
		//打开Excel，获取sheet的集合
		HSSFWorkbook hssfWorkbook = null;
		List<PumpspectrumData> pumpspectrumDataList = new LinkedList<PumpspectrumData>();
		try {
			hssfWorkbook = new HSSFWorkbook(new FileInputStream(fileName));
		} catch (IOException e) {
			e.printStackTrace();
		}
		Iterator<Sheet> sheetIterator = hssfWorkbook.iterator();

		//List<PumpTestSourceData> list = new LinkedList<PumpTestSourceData>();
        
		//遍历Excel文件的集合。
		while (sheetIterator.hasNext()) {
			PumpspectrumData pumpspectrumData = new PumpspectrumData();
			Sheet sheet = sheetIterator.next();
            
			//获取基本的信息
			String type = ExcelUtil.readStringCellValue(sheet,1,0);
			Integer poles = ExcelUtil.readIntCellValue(sheet,1,1);
			Integer frequency = ExcelUtil.readIntCellValue(sheet,1,2);
			Integer speed = ExcelUtil.readIntCellValue(sheet,1,3);
			Integer stageNum = ExcelUtil.readIntCellValue(sheet,1,4);
			Integer interNum = ExcelUtil.readIntCellValue(sheet,1,5);
			Double mcsf = ExcelUtil.readDoubleCellValue(sheet,1,6);
			String impellerMaterial = ExcelUtil.readStringCellValue(sheet,3,0);
        
			pumpspectrumData.setType(type);
			pumpspectrumData.setPoles(poles);
			pumpspectrumData.setFrequency(frequency);
			pumpspectrumData.setRotorSpeed(speed);
			pumpspectrumData.setStageNum(stageNum);
			pumpspectrumData.setInterNum(interNum);
			pumpspectrumData.setmCSF(mcsf);
			pumpspectrumData.setImpellerMaterial(impellerMaterial);

			int initRow = 10;
			//int step = 20;
			//Double min = Double.MAX_VALUE, max = Double.MIN_VALUE;
            
			//获取测试数据，包括流量扬程，流量功率，流量效率，流量汽蚀的测试数据
			List<PumpTestSourceData> testSourceDataList = new LinkedList<PumpTestSourceData>();
            
			for (int i = 0; i < 10; i++) {
				int row = initRow+ 20*i;
				PumpTestSourceData testSourceData = readPowerData(sheet, row);
				if(testSourceData==null)
				{
					break;
				}
				testSourceDataList.add(testSourceData);
			}
            
			
			testSourceDataList.sort(new Comparator<PumpTestSourceData>() {

				public int compare(PumpTestSourceData o1, PumpTestSourceData o2) {
					return Double.compare(o1.getDia(), o2.getDia());
				}

			});

			int size = testSourceDataList.size();

			Double minDia = testSourceDataList.get(0).getDia();
			Double maxDia = testSourceDataList.get(size - 1).getDia();

			pumpspectrumData.setMinDia(minDia);
			pumpspectrumData.setMaxDia(maxDia);

			pumpspectrumData.setTestSourceDataList(testSourceDataList);
			pumpspectrumDataList.add(pumpspectrumData);
		}

		try {
			hssfWorkbook.close();
		} catch (IOException e) 
		{
			e.printStackTrace();
		}

		return pumpspectrumDataList;

	}

	public static Double[] getCaculateParams(List<Map<String, Double>> testDataList, String key, int paramCount) {
		Map<String, Object> env = new HashMap<String, Object>();

		int lenth = testDataList.size();

		for (int i = 0; i < lenth; i++) {
			Map<String, Double> m = testDataList.get(i);
			env.put("q" + i, m.get("q"));
			env.put(key + i, m.get(key));
		}

		double[][] leftMatrix = new double[paramCount][paramCount];
		for (int i = 0; i < paramCount; i++) {
			for (int j = 0; j < paramCount; j++) {
				String express = getExpressLeft(i, j, lenth, "q");
				leftMatrix[i][j] = (Double) AviatorEvaluator.compile(express).execute(env);
			}
		}

		double[][] rightMatrix = new double[paramCount][1];

		for (int i = 0; i < paramCount; i++) {
			String express = getExpressRight(i, lenth, "q", key);
			rightMatrix[i][0] = (Double) AviatorEvaluator.compile(express).execute(env);

		}

		// 历史性的时刻到来了，矩阵方程求解！
		Matrix A = new Matrix(leftMatrix);
		Matrix b = new Matrix(rightMatrix);
		Matrix x = A.solve(b);

		Double[] param = new Double[paramCount];
		for(int i =0 ;i < paramCount;i++)
		{
			param[paramCount-1-i] = x.get(i, 0);
		}
		/*param[4] = x.get(0, 0);
		param[3] = x.get(1, 0);
		param[2] = x.get(2, 0);
		param[1] = x.get(3, 0);
		param[0] = x.get(4, 0);*/
		return param;

	}

	private static String getExpressRight(int k, int splitCount, String xKeyValue, String yKeyValue) {
		StringBuilder sb = new StringBuilder();

		sb.append("math.pow(" + xKeyValue + "0," + k + ")*" + yKeyValue + "0");
		for (Integer i = 1; i < splitCount; i++) {
			sb.append("+math.pow(" + xKeyValue + i.toString() + "," + k + ")*" + yKeyValue + i.toString());
		}
		return sb.toString();
	}

	private static String getExpressLeft(int k, int j, int splitCount, String xKeyValue) {
		StringBuilder sb = new StringBuilder();

		int p = k + j;

		sb.append("math.pow(" + xKeyValue + "0," + p + ")");
		for (Integer i = 1; i < splitCount; i++) {
			sb.append("+math.pow(" + xKeyValue + i.toString() + "," + p + ")");
		}
		return sb.toString();
	}
}
